package com.study.jdbc.dao.impl;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.study.jdbc.dao.CustomerDao;
import com.study.jdbc.domain.Customer;
import com.study.jdbc.utils.DBCPUtil;

/**
* @Name: CustomerDaoJDBCImpl
* @Description: 用户CRUD操作dao接口JDBC实现类：使用DBUtils工具类（使用数据源）
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-16 下午7:43:46 
* @Version: V1.0
 */
public class CustomerDaoJDBCImpl3 implements CustomerDao {
	
	private QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource()) ;

	@Override
	public Customer findCustomerById(Serializable id) {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer " +
				"where id = ? ; " ;
		Object[] params = {id} ;
		Customer customer = null ;
		try {
			customer = (Customer) queryRunner.query(sql, new BeanHandler<Customer>(Customer.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customer ;
	}

	@Override
	public List<Customer> findAllCustomers() {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description from t_customer ; " ;
		List<Customer> list = null ;
		try {
			list = (List<Customer>) queryRunner.query(sql, new BeanListHandler<Customer>(Customer.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list ;
	}

	@Override
	public void saveCustomer(Customer customer) {
		String sql = "insert into " +
				" t_customer(id, name, gender, birthday, cellphone, email, preference, type, description) " +
				" values(?, ?, ?, ?, ?, ?, ?, ?, ?) ; " ;
		try {
			queryRunner.update(sql, 
					customer.getId(),
					customer.getName(),
					customer.getGender(),
					customer.getBirthday(),
					customer.getCellphone(),
					customer.getEmail(),
					customer.getPreference(),
					customer.getType(),
					customer.getDescription()) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateCustomer(Customer customer) {
		String sql = "update t_customer set " +
				" name = ?, " +
				" gender = ?, " +
				" birthday = ?, " +
				" cellphone = ?, " +
				" email = ?, " +
				" preference = ?, " +
				" type = ?, " +
				" description = ? " +
				" where id = ? ; " ;
		try {
			queryRunner.update(sql, 
					customer.getName(),
					customer.getGender(),
					customer.getBirthday(),
					customer.getCellphone(),
					customer.getEmail(),
					customer.getPreference(),
					customer.getType(),
					customer.getDescription(),
					customer.getId()) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void deleteCustomerById(Serializable id) {
		String sql = "delete from t_customer where id = ? ; " ;
		try {
			queryRunner.update(sql, id) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public int getTotalRecordsCount() {
		return findAllCustomers().size() ;
	}

	@Override
	public List<Customer> findPageRecords(int startIndex, int pageSize) {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description" +
				" from t_customer" +
				" limit ?, ? ; " ;
		List<Customer> list = null ;
		try {
			list = (List<Customer>) queryRunner.query(sql, new BeanListHandler<Customer>(Customer.class), startIndex, pageSize);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list ;
	}
}


